clear all
set more off

capture log close

*global path "C:\DATA\Dropbox\Bank_robberies\Technology\Replication"
cd "$path"

log using "$path/Tables/estimation.log", replace



/***  TABLES 2 & 3 FOR PAPER ***/

*local opt "dec(4) tex(frag) lab nocons"
local opt "dec(4) excel lab nocons"

local tot=20
tempfile temp
use "Data/proprietary/01 Master Data" if neighbor==1, clear 
drop chiave_agenzia2 km_to_nid unsuccessful2 attempted2 guardia2 flag2 codiceabi2 /*
*/ninvisible2 nvisible2 
sort chiave_agenzia1 year
tsset chiave_agenzia1 year 



gen chara1=nvisible1+ninvisible1
drop if chara1==0 /// drop if the bank has no info on security devices

sort chiave_agenzia1 year

gen glat=int(latitude/0.01/50)
gen glong=int(longitude/0.01/50)
bysort glat glong year: gen x=_n==1
gen cluster50=sum(x)
drop x glat glong

* Keep only info on devices that show up at leasy 1% of the time
foreach y of varlist device*{
replace `y'=0 if `y'==.
qui su `y'
if r(mean)<.01{
	drop `y'
	}
}



sort cluster50
foreach y of varlist attempted1 guardia1 device*{
by cluster50: egen x=mean(`y') 
qui gen DM`y'=`y'-x
qui gen CV`y'=sqrt(x*(1-x))
drop x
}

sort chiave_agenzia year
foreach y of varlist attempted1 guardia1 device* chara1{
gen D`y'=`y'-l.`y'
}

gen TCV=0
foreach y of varlist  guardia1 device* {
replace TCV=TCV+CV`y'
}

foreach y of varlist Ddevice*{
pwcorr Dguardia1 `y' , sig
}
pwcorr Dguardia1 Dchara1 , sig
drop  DMattempted1 DMguardia1 DMdevice* Dattempted1 Dguardia1 Ddevice* CV*



qui egen sattempted1=mean(attempted1), by(provinc) 
qui su sattempted1, de
local rp50=r(p50)



* MUNICIPALITIES WITH MORE THAN 0.5% OF OBSERVATIONS
bysort comune: gen n=_N/245712
tab comune if n>0.003&n<=0.005
drop n

gen populous_province=provincia=="BARI"|provincia=="BOLOGNA"| /*
*/provincia=="BRESCIA"|provincia=="FIRENZE"|provincia=="GENOVA"| /*
*/provincia=="MILANO"| /*
*/provincia=="NAPOLI"|provincia=="PADOVA"|provincia=="PALERMO"| /*
*/provincia=="PARMA"|provincia=="ROMA"|provincia=="TORINO"| /*
*/provincia=="VENEZIA"|provincia=="VERONA"| /*
*/provincia=="ANCONA"|provincia=="AREZZO"|provincia=="BERGAMO"|/*
*/provincia=="CATANIA"|provincia=="FERRARA"|/*
*/provincia=="MODENA"|provincia=="PERUGIA"|/*
*/provincia=="PIACENZA"|provincia=="PRATO"|/*
*/provincia=="RAVENNA"|provincia=="REGGIO NELL'EMILIA"|provincia=="TRIESTE"|/*
*/provincia=="VICENZA"


pwcorr guardia1 chara1, sig
reghdfe guardia1, absorb(FE_YEAR=i.year FE_FIRM=i.chiave_agenzia1) resid
predict eguardia1, resid
drop FE*

reghdfe chara1, absorb(FE_YEAR=i.year FE_FIRM=i.chiave_agenzia1) resid
predict echara1, resid
drop FE*


pwcorr echara1 eguardia1, sig 
drop echara eguardia
save `temp', replace

******start skip

tsset chiave_agenzia1 year
local step=0
local cluster "cluster50"
*local cluster "idprovincia"

reghdfe attempted1 guardia1, absorb(year) cluster(`cluster')
outreg2 using "Tables/deterrence", `opt' replace

reghdfe attempted1 guardia1 if guardia1==l.guardia1 , absorb(year) cluster(`cluster')
outreg2 using "Tables/deterrence2", `opt' replace

reg attempted1 device*
predict hattempted1 

tab guardia1, su(chara1)
tab guardia1, su(hattempted)  
drop hattempted

 
 
foreach n in 800 400 200 100 50 25 10 5 2 1 .5 .25{
*foreach n in .5 {
 local step=`step'+1
 qui use `temp', clear
 qui gen glat=int(latitude/0.01/`n')
 qui gen glong=int(longitude/0.01/`n')
 bysort glat glong: gen x=_n==1
 qui gen ggroup`step'=sum(x)
 drop x glat glong
 
 sort chiave_agenzia1 year

 qui egen guard_tot=sum(guardia1), by(ggroup`step' year)
 qui egen bank_tot=count(ggroup`step'), by(ggroup`step' year)
 
 qui egen guardia_abi=sum(guardia1), by(cluster50 codiceabi1 year)
 qui egen abi_tot=count(ggroup`step'), by(cluster50 codiceabi1 year)
 
 *replace guardia_abi_tot=sum(guardia_abi), by(ggroup`n' year)

 qui gen guardia01=((guard_tot-guardia1)/(bank_tot-1)) 
 qui replace guardia01=0 if guardia01==.
 qui gen guardia11=((guard_tot-guardia1)/(bank_tot-1))*(guardia1)
 qui gen no_substitutes=guardia11==.
 qui replace guardia11=0 if guardia11==.

 
 qui replace guardia_abi=(guardia_abi-guardia1)/(abi_tot-1)
 qui gen guardia_abi11=guardia_abi*guardia1
 
 *gen guardia_abi01=((guard_tot-guardia1)/(bank_tot-1)) // *(1-guardia1) 
 *gen guardia_abi11=((guard_tot-guardia1)/(bank_tot-1))*(guardia1)
 
 qui egen chara1_tot=sum(chara1), by(ggroup`step' year)
 qui egen ninvisible1_tot=sum(ninvisible1), by(ggroup`step' year)
 qui egen nvisible1_tot=sum(nvisible1), by(ggroup`step' year)
 qui egen attempted1_tot=sum(attempted1), by(ggroup`step' year)
 

 qui gen chara2=(chara1_tot-chara1)/(bank_tot-1)
 qui gen ninvisible2=(ninvisible1_tot-ninvisible1)/(bank_tot-1) 
 qui gen nvisible2=(nvisible1_tot-nvisible1)/(bank_tot-1)
 qui gen ninvisible2_tot=(ninvisible1_tot-ninvisible1)/100
 qui gen attempted2=(attempted1_tot-attempted1)/(bank_tot-1)

 qui replace nvisible2=0 if nvisible2==.
 qui replace ninvisible2=0 if ninvisible2==.
 qui replace chara2=0 if chara2==.
 
 qui drop chara1_tot ninvisible1_tot nvisible1_tot
 rename idprov idprovincia

 * Jump squares
 lab var attempted1 "Number of Robberies" 
 lab var attempted2 "Average Num. of Robb. against Neighbors" 
 lab var guardia1 "Guard"
 lab var guardia01  "% Neighbors with Guards" 
 lab var guardia11  "Guard $\times$ % Neighbors with Guards"
 lab var chara1 "Number of Security Devices"
 lab var chara2 "Neighbors Average Num. of Sec. Devices"
 
 lab var nvisible1 "Number of Visible Security Devices"
 lab var nvisible2 "Neighbors Average Num. of Visible Sec. Devices"

 lab var ninvisible1 "Number of Invisible Security Devices"
 lab var ninvisible2 "Neighbors Average Num. of Invisible Sec. Devices"

 lab var no_sub "No substitute bank"
 qui gen switch_year=guardia1!=l.guardia1
 qui gen switch_neighbor=guardia01!=l.guardia01
 qui gen noswitch_market=switch_year==0&switch_neighbor==0
 di `n'
 su noswitch_market
 
 *keep if switch_year==0
 *keep if switch_neighbor==0
 
gen d_attempted=abs(attempted1-attempted2)
gen d_guardia=abs(guardia1-guardia01)
gen d_chara=abs(chara1-chara2)

*su d_*
*}


/***** SKIP SQUARES */
	* adds market fixed effects
 reghdfe attempted1 guardia1 , absorb(ggroup`step'  year) cluster(`cluster')
 local n=`e(df_a)'-10
 outreg2 using "tables/deterrence", `opt'  addstat(# Square fixed effects, `n')

 reghdfe attempted1 guardia1 if guardia1==l.guardia1 , absorb(ggroup`step'  year) cluster(`cluster')
 local n=`e(df_a)'-10
 outreg2 using "tables/deterrence2", `opt' addstat(# Square fixed effects, `n') 
 
 
 reghdfe attempted1 guardia1 guardia01 guardia11 no_substitutes, absorb(ggroup`step' year) cluster(`cluster')
 test guardia01+guardia11==0
 local rp=r(p)
 qui su guardia01 if e(sample)
 local b=r(mean)*_b[guardia11]+_b[guardia1]
 su bank_tot if e(sample)
 local rneigh=r(mean)-1 

 if `n'==50 {
  outreg2 using "Tables/market_fe", `opt' addstat("$\beta_1+\beta_3 \overline{g}_{-ijt}$", `b', t-stat,`rp', Average n. of neighb., `rneigh') replace cti("`n'km squares")

  su no_substitutes, sep(0)
  su no_substitutes if sattempted1<=`rp50' , sep(0)
  su no_substitutes if sattempted1>`rp50' , sep(0)
 }
 else {
  outreg2 using "Tables/market_fe", `opt' addstat("$\beta_1+\beta_3 \overline{g}_{-ijt}$", `b', t-stat,`rp', Average n. of neighb., `rneigh') cti("`n'km squares")
 }
 
 reghdfe attempted1 guardia1 guardia01 guardia11 no_substitutes if populous, absorb(ggroup`step' year) cluster(`cluster')
 test guardia01+guardia11==0
 local rp=r(p)
 qui su guardia01 if e(sample)
 local b=r(mean)*_b[guardia11]+_b[guardia1]
 su bank_tot if e(sample)
 local rneigh=r(mean)-1

 if `n'==50 {
  outreg2 using "Tables/market_fe_populous", `opt' addstat("$\beta_1+\beta_3 \overline{g}_{-ijt}$", `b', t-stat,`rp', Average n. of neighb., `rneigh') replace cti("`n'km squares")

  su no_substitutes, sep(0)
  su no_substitutes if sattempted1<=`rp50' , sep(0)
  su no_substitutes if sattempted1>`rp50' , sep(0)
 }
 else {
  outreg2 using "Tables/market_fe_populous", `opt' addstat("$\beta_1+\beta_3 \overline{g}_{-ijt}$", `b', t-stat,`rp', Average n. of neighb., `rneigh') cti("`n'km squares")
 }
 
 
  reghdfe attempted1 guardia1 guardia01 guardia11 no_substitutes, absorb(chiave_agenzia1 year) cluster(`cluster')
  test guardia01+guardia11==0
 if `n'==50 {
  outreg2 using "Tables/branch_market_fe", `opt' addstat(t-stat,`r(p)') replace  cti("`n'km squares")
 }
 else {
  outreg2 using "Tables/branch_market_fe", `opt' addstat(t-stat,`r(p)')  cti("`n'km squares")
 }
}




local opt "dec(4) excel lab nocons"


 reghdfe attempted1 guardia1 , absorb(chiave_agenzia1  year) cluster(`cluster')
 local n=`e(df_a)'-10
 outreg2 using "tables/deterrence", `opt'  addstat(# Square fixed effects, `n')
 
 reghdfe attempted1 guardia1 if guardia1==l.guardia1, absorb(chiave_agenzia1  year) cluster(`cluster')
 local n=`e(df_a)'-10
 outreg2 using "tables/deterrence", `opt' addstat(# Square fixed effects, `n')

*****end skip*****/

 




qui use `temp', clear
 
 
tsset chiave_agenzia1 year
local cluster "cluster50"
*local cluster "idprovincia"


qui gen glat=int(latitude/0.01/.5)
qui gen glong=int(longitude/0.01/.5)
bysort glat glong: gen x=_n==1
qui gen ggroup1=sum(x)
drop x glat glong
 
sort chiave_agenzia1 year
qui egen guard_tot=sum(guardia1), by(ggroup1 year)
qui egen bank_tot=count(ggroup1), by(ggroup1 year)
 
qui egen guardia_abi=sum(guardia1), by(cluster50 codiceabi1 year)
qui egen abi_tot=count(ggroup1), by(cluster50 codiceabi1 year)
 
 *replace guardia_abi_tot=sum(guardia_abi), by(ggroup`n' year)

qui gen guardia01=((guard_tot-guardia1)/(bank_tot-1)) 
qui replace guardia01=0 if guardia01==.
qui gen guardia11=((guard_tot-guardia1)/(bank_tot-1))*(guardia1)
qui gen no_substitutes=guardia11==.
qui replace guardia11=0 if guardia11==.
 
corr guardia1 guardia01 guardia11


 
qui replace guardia_abi=(guardia_abi-guardia1)/(abi_tot-1)
qui gen guardia_abi11=guardia_abi*guardia1

*gen guardia_abi01=((guard_tot-guardia1)/(bank_tot-1)) // *(1-guardia1) 
 *gen guardia_abi11=((guard_tot-guardia1)/(bank_tot-1))*(guardia1)
 
qui egen chara1_tot=sum(chara1), by(ggroup1 year)
qui egen ninvisible1_tot=sum(ninvisible1), by(ggroup1 year)
qui egen nvisible1_tot=sum(nvisible1), by(ggroup1 year)
qui egen attempted1_tot=sum(attempted1), by(ggroup1 year)
 

qui gen chara2=(chara1_tot-chara1)/(bank_tot-1)
qui gen ninvisible2=(ninvisible1_tot-ninvisible1)/(bank_tot-1) 
qui gen nvisible2=(nvisible1_tot-nvisible1)/(bank_tot-1)
qui gen ninvisible2_tot=(ninvisible1_tot-ninvisible1)/100
qui gen attempted2=(attempted1_tot-attempted1)/(bank_tot-1)

qui replace nvisible2=0 if nvisible2==.
qui replace ninvisible2=0 if ninvisible2==.
qui replace chara2=0 if chara2==.
qui replace attempted2=0 if attempted2==.
 
qui drop chara1_tot ninvisible1_tot nvisible1_tot
rename idprov idprovincia


su year attempted1 guardia1  chara1  no_substitutes, sep(0)
su year attempted1 guardia1  chara1  no_substitutes if sattempted1<=`rp50' , sep(0)
su year attempted1 guardia1  chara1  no_substitutes if sattempted1>`rp50' , sep(0)

 
 * Jump squares
 lab var attempted1 "Number of Robberies" 
 lab var attempted2 "Average Num. of Robb. against Neighbors" 
 lab var guardia1 "Guard"
 lab var guardia01  "% Neighbors with Guards" 
 lab var guardia11  "Guard $\times$ % Neighbors with Guards"
 lab var chara1 "Number of Security Devices"
 lab var chara2 "Neighbors Average Num. of Sec. Devices"
 
 lab var nvisible1 "Number of Visible Security Devices"
 lab var nvisible2 "Neighbors Average Num. of Visible Sec. Devices"

 lab var ninvisible1 "Number of Invisible Security Devices"
 lab var ninvisible2 "Neighbors Average Num. of Invisible Sec. Devices"

 lab var no_sub "No substitute bank"
 qui gen switch_year=guardia1!=l.guardia1
 qui gen switch_neighbor=guardia01!=l.guardia01
 qui gen noswitch_market=switch_year==0&switch_neighbor==0
 su noswitch_market
 
 *keep if switch_year==0
 *keep if switch_neighbor==0
 
gen d_attempted=abs(attempted1-attempted2)
gen d_guardia=abs(guardia1-guardia01)
gen d_chara=abs(chara1-chara2)

*su d_*
*}

/***** SKIP SQUARES */
	* adds market fixed effects

 reghdfe attempted1 guardia1 guardia01 guardia11 no_substitutes, absorb(ggroup1 year) cluster(`cluster')
 test guardia01+guardia11==0
 local rp=r(p)
 qui su guardia01 if e(sample)
 local b=r(mean)*_b[guardia11]+_b[guardia1]
 su bank_tot if e(sample)
 local rneigh=r(mean)-1 
 gen year2=year^2

 **** skip***
 * Preferred Specs  
 
 reghdfe attempted1 guardia1 guardia01 guardia11 no_substitutes, absorb(ggroup1 year) /*
	*/cluster(`cluster')
 test guardia01+guardia11==0
 outreg2 using "Tables/market_fe_controls_pref_spec", `opt' addstat(t-stat,`r(p)') replace

 reghdfe attempted1 guardia1 guardia01 guardia11 chara1 chara2 no_substitutes, /*
	*/ absorb(ggroup1 year) cluster(`cluster')
 test guardia01+guardia11==0
 outreg2 using "Tables/market_fe_controls_pref_spec", `opt' addstat(t-stat,`r(p)') 

  **** Robust
 
 reghdfe attempted1 guardia1 guardia01 guardia11 no_substitutes, absorb(ggroup1#c.year year) /*
	*/cluster(`cluster')
 test guardia01+guardia11==0
 outreg2 using "Tables/market_fe_controls_pref_spec", `opt' addstat(t-stat,`r(p)') 


 
 reghdfe attempted1 guardia1 guardia01 guardia11 no_substitutes, /*
 */ absorb(ggroup1#c.year ggroup1#c.year2 year) cluster(`cluster')
 test guardia01+guardia11==0
 outreg2 using "Tables/market_fe_controls_pref_spec", `opt' addstat(t-stat,`r(p)') 

 test guardia01+guardia11==0
 outreg2 using "Tables/market_fe_controls_robust", `opt' replace addstat(t-stat,`r(p)')  

 
 
 reghdfe attempted1 guardia1 guardia01 guardia11 no_substitutes if year<=2008, /*
	*/absorb(ggroup1#c.year ggroup1#c.year2 year) cluster(`cluster')
 test guardia01+guardia11==0
 outreg2 using "Tables/market_fe_controls_robust", `opt' addstat(t-stat,`r(p)')  

 su guardia11
 gen guardia112=(guardia11-r(mean))^2
 su guardia01
 gen guardia012=(guardia01-r(mean))^2

 * skip
 reghdfe attempted1 guardia1 guardia01 guardia11 guardia012 guardia112 no_substitutes, /*
	*/ absorb(ggroup1#c.year ggroup1#c.year2 year) cluster(`cluster')
 test guardia01+guardia11==0
 outreg2 using "Tables/market_fe_controls_robust", `opt' addstat(t-stat,`r(p)') 

 reghdfe attempted1 guardia1 guardia01 guardia11 l.attempted1 no_substitutes,/*
 */absorb(ggroup1#c.year ggroup1#c.year2 year) cluster(`cluster')
 test guardia01+guardia11==0
 outreg2 using "Tables/market_fe_controls_robust", `opt' addstat(t-stat,`r(p)') 


 reghdfe attempted1 guardia1 guardia01 guardia11 l.attempted1 l.attempted2 no_substitutes,/*
 */absorb(ggroup1#c.year ggroup1#c.year2 year) cluster(`cluster')
  test guardia01+guardia11==0
 outreg2 using "Tables/market_fe_controls_robust", `opt' addstat(t-stat,`r(p)') 

local cluster "cluster50"
local opt "dec(4) excel lab nocons"

reghdfe attempted1 guardia1 guardia01 guardia11 chara1 chara2 no_substitutes,/*
	*/absorb(ggroup1#c.year year) cluster(`cluster')
test guardia01+guardia11==0
outreg2 using "Tables/market_fe_subst", `opt' replace addstat(t-stat,`r(p)') 

reghdfe attempted1 guardia1 guardia01 guardia11 chara1 chara2 no_substitutes if TCV<= 12.8 ,/*
	*/absorb(ggroup1#c.year year) cluster(`cluster')
test guardia01+guardia11==0
outreg2 using "Tables/market_fe_subst", `opt' addstat(t-stat,`r(p)') 
 
reghdfe attempted1 guardia1 guardia01 guardia11 chara1 chara2 no_substitutes if TCV>= 12.8 ,/*
	*/absorb(ggroup1#c.year year) cluster(`cluster')
test guardia01+guardia11==0
outreg2 using "Tables/market_fe_subst", `opt' addstat(t-stat,`r(p)') 
 
 
 
*Correlation between past robberies and the hiring or firing of guards 

gen Dguardia1=guardia1-l.guardia1

 reghdfe Dguardia1 l.attempted1 no_substitutes,/*
 */absorb(year) cluster(`cluster')

  reghdfe Dguardia1 l.attempted2 no_substitutes,/*
 */absorb(year) cluster(`cluster')

  reghdfe Dguardia1 l.attempted1 l.attempted2 no_substitutes,/*
 */absorb(year) cluster(`cluster')

  reghdfe Dchara1 l.attempted1 l.attempted2 no_substitutes,/*
 */absorb(year) cluster(`cluster')

 reghdfe attempted1 l.attempted1 l.attempted2 no_substitutes,/*
 */absorb(year) cluster(`cluster')

 * This is to show that there is very little variation left once we condition on a no-switch
  reghdfe guardia01 guardia1  guardia11 no_substitutes if noswitch,/*
	*/absorb(chiave_agenzia1 year) cluster(`cluster')

 reghdfe guardia01  guardia1 guardia11 no_substitutes if noswitch,/*
	*/absorb(ggroup1 year) cluster(`cluster')

 reghdfe attempted1 guardia1 guardia01 guardia11 no_substitutes if switch_year==0,/*
	*/absorb(chiave_agenzia1 year) cluster(`cluster')

 reghdfe attempted1 guardia1 guardia01 guardia11 chara1 chara2  no_substitutes if noswitch,/*
	*/absorb(chiave_agenzia1 year) cluster(`cluster')



log close
